<?php

class Controller_Triggerday extends Controller{
    public function Action_insertSkuQtyAndPrice(){
        set_time_limit(0);
        //当天日期
        $date=date('y-m-d',time());//var_dump($date);exit;
        //本月期出日期
        $startDate=date('y-m-01',time());//var_dump($date);exit;//
        //skusystem_skus中所有可用sku
        $sql='SELECT `id`,`sku` FROM `skusystem_skus` WHERE `isavailable`=1';
        $skusArrayAll=DB::query(Database::SELECT,$sql)->execute('newerp')->as_array('id','sku');//->compile('newerp');//
        $skusAll=array_values($skusArrayAll);
        //今天已经插入SkuQtyAndPrice表的sku
        $sql='SELECT `sku` FROM `SkuQtyAndPrice` WHERE `inventory_date`="'.$date.'"';
        $skusArrayInSkusQtyAndPrice=DB::query(Database::SELECT,$sql)->execute('newerp')->as_array('sku');//->compile('newerp');//
        $skusInSkusQtyAndPrice=array_keys($skusArrayInSkusQtyAndPrice);
        //需要执行插入的sku
        $skusOpreate=array_diff($skusAll,$skusInSkusQtyAndPrice);
        if(empty($skusOpreate)){
            echo '1.0:同步结束!'.'<br/>';
            //exit;
        }else{
            //计算当前库存
            $sql='SELECT i.`sku`,i.`quantity` FROM(
                SELECT inventories.`sku` sku,inventories.`quantity` quantity
                FROM `inventories`
                WHERE inventories.`inventory_date`="'.$startDate.'"
                AND inventories.`warehouseid`!=101
                AND inventories.`sku` IN ("'.implode('","',$skusOpreate).'")
                UNION ALL
                SELECT warehouseorders.`sku` sku,SUM(warehouseorders.`warehouse_quantity`) quantity
                FROM `warehouseorders`
                WHERE warehouseorders.`warehouse_date`>="'.$startDate.'"
                AND warehouseorders.`warehouse_date`<"'.$date.'"
                AND warehouseorders.`warehouseid`!=101
                AND warehouseorders.`sku` IN ("'.implode('","',$skusOpreate).'")
                 UNION ALL
                SELECT deliveryorders.`sku` sku,-SUM(deliveryorders.`quantity`) quantity
                FROM `deliveryorders`
                WHERE deliveryorders.`delivery_date`>="'.$startDate.'"
                AND deliveryorders.`delivery_date`<"'.$date.'"
                AND deliveryorders.`warehouseid`!=101
                AND deliveryorders.`sku` IN ("'.implode('","',$skusOpreate).'")
                ) i
                WHERE 1
                GROUP BY i.`sku`';
            $inventories=DB::query(Database::SELECT,$sql)->execute('newerp')->as_array('sku','quantity');//->compile('newerp');//

            //计算当前在途
            $sql='SELECT UPPER(sku) AS sku,SUM(quantity-ware_quantity) AS owaynum
                FROM  purchaseorder_details WHERE status>10
                AND status<70
                AND `sku` IN ("'.implode('","',$skusOpreate).'")
                GROUP BY sku';
            $onways=DB::query(Database::SELECT,$sql)->execute('newerp')->as_array('sku','owaynum');//->compile('newerp');//

            //sku基础资料库查询sku供应商、单价
            $sql="SELECT `sku`,`supplier_id`
              FROM `skusystem_supplier_skus`
              WHERE `isavailable`=1
              AND `sku` IN ('".implode("','",$skusOpreate)."')
              ORDER BY `sku`";
            $skus_supplier=DB::query(Database::SELECT,$sql)->execute('newerp')->as_array('sku','supplier_id');//->compile('newerp');//

            $sql="SELECT `sku`,`num`
              FROM `skusystem_price`
              WHERE sku IN ('".implode("','",$skusOpreate)."')
              ORDER BY `sku`";
            $skus_price=DB::query(Database::SELECT,$sql)->execute('newerp')->as_array('sku','num');//->compile('newerp');//

            $skusinfo=array();
            foreach ($skusOpreate as $sku) {
                $skusinfo[$sku]['inventory']=isset($inventories[$sku])?$inventories[$sku]:-1;
                $skusinfo[$sku]['onway']=isset($onways[$sku])?$onways[$sku]:-1;
                $skusinfo[$sku]['supplier']=isset($skus_supplier[$sku])?$skus_supplier[$sku]:-1;
                $skusinfo[$sku]['price']=isset($skus_price[$sku])?$skus_price[$sku]:-1;
            }

            foreach($skusinfo as $sku=>$skuinfo){
                try{
                    $sql='INSERT INTO `SkuQtyAndPrice` (`sku`,`inventory_date`,`quantity`,`canuseqty`,`supplier_id`,`price`)
                    VALUES("'.$sku.'","'.$date.'",'.$skuinfo['inventory'].','.$skuinfo['onway'].','.$skuinfo['supplier'].','.$skuinfo['price'].')';
                    $sign[$sku]=DB::query(Database::INSERT,$sql)->execute('newerp');//->compile('newerp');exit;//

                }catch (Exception $e){
                    echo $sku.':'.$e->getMessage().'<br/>';
                    echo '--------------------------------------------------<br/>';
                    continue;
                }
            }
            echo '2.0:同步结束!'.'<br/>';
        }

        $sql='DELETE FROM `SkuQtyAndPrice` WHERE `inventory_date` <"'.$date.'"';
        $sign=DB::query(Database::DELETE,$sql)->execute('newerp');//->compile('newerp');exit;//->compile('newerp');//
        echo '3.0:删除历史sku记录:'.$sign.'条！';
        exit;
    }
}